
Imports System.Data.SqlClient
Imports Crystal.DatabaseManagement

Namespace BusinessLayer

    Public Class cGroups

#Region "Declare"

        Private iGroupId As Integer
        Private strGroupName As String
        Private strCustodianName As String
        Private iCustodianID As Integer

        Private iAddressID As Integer
        Private iContactID As Integer
        Private iEntityID As Integer
        Private iEntityValueID As Integer
        Private strAddress As String
        Private strAddress2 As String
        Private strCity As String
        Private strState As String
        Private strCounty As String
        Private strCountry As String
        Private strRegion As String
        Private strPostCode As String
        Private strEmailAddress As String
        Private blnActive As Boolean
        '        Private blnIsPrimary As Boolean
        Private mintPhoneTypeId As Integer
        Private iCountryID As Integer
        Private mblnMaster As Boolean
#End Region

#Region "Properties"

        Public Property GroupID() As Integer
            Get
                Return iGroupId
            End Get
            Set(ByVal value As Integer)
                iGroupId = value
            End Set
        End Property

        Public Property GroupName() As String
            Get
                Return strGroupName
            End Get
            Set(ByVal value As String)
                strGroupName = value
            End Set
        End Property
        Public Property Master() As Boolean
            Get
                Return mblnMaster
            End Get
            Set(ByVal value As Boolean)
                mblnMaster = value
            End Set
        End Property

        Public Property CustodianName() As String
            Get
                Return strCustodianName
            End Get
            Set(ByVal value As String)
                strCustodianName = value
            End Set
        End Property
       
        Public Property State() As String
            Get
                Return strState
            End Get
            Set(ByVal value As String)
                strState = value
            End Set
        End Property

        Public Property AddressID() As Integer
            Get
                Return iAddressID
            End Get
            Set(ByVal value As Integer)
                iAddressID = value
            End Set
        End Property

        Public Property CustodianID() As Integer
            Get
                Return iCustodianID
            End Get
            Set(ByVal value As Integer)
                iCustodianID = value
            End Set
        End Property

        Public Property ContactID() As Integer
            Get
                Return iContactID
            End Get
            Set(ByVal value As Integer)
                iContactID = value
            End Set
        End Property

        Public Property EmailAddress() As String
            Get
                Return strEmailAddress
            End Get
            Set(ByVal value As String)
                strEmailAddress = value
            End Set
        End Property

        Public Property Address() As String
            Get
                Return strAddress
            End Get
            Set(ByVal value As String)
                strAddress = value
            End Set
        End Property

        Public Property Address2() As String
            Get
                Return strAddress2
            End Get
            Set(ByVal value As String)
                strAddress2 = value
            End Set
        End Property

        Public Property City() As String
            Get
                Return strCity
            End Get
            Set(ByVal value As String)
                strCity = value
            End Set
        End Property

        Public Property Country() As String
            Get
                Return strCountry
            End Get
            Set(ByVal value As String)
                strCountry = value
            End Set
        End Property

        Public Property Region() As String
            Get
                Return strRegion
            End Get
            Set(ByVal value As String)
                strRegion = value
            End Set
        End Property

        Public Property County() As String
            Get
                Return strCounty
            End Get
            Set(ByVal value As String)
                strCounty = value
            End Set
        End Property

        Public Property PostCode() As String
            Get
                Return strPostCode
            End Get
            Set(ByVal value As String)
                strPostCode = value
            End Set
        End Property

        Public Property Active() As Boolean
            Get
                Return blnActive
            End Get
            Set(ByVal value As Boolean)
                blnActive = value
            End Set
        End Property

        Public Property PhoneTypeId() As Integer
            Get
                Return mintPhoneTypeId
            End Get
            Set(ByVal value As Integer)
                mintPhoneTypeId = value
            End Set
        End Property

        Public Property EntityId() As Integer
            Get
                Return iEntityID
            End Get
            Set(ByVal value As Integer)
                iEntityID = value
            End Set
        End Property

        Public Property EntityValueId() As Integer
            Get
                Return iEntityValueID
            End Get
            Set(ByVal value As Integer)
                iEntityValueID = value
            End Set
        End Property

        Public Property CountryID() As Integer
            Get
                Return iCountryID
            End Get
            Set(ByVal value As Integer)
                iCountryID = value
            End Set
        End Property

#End Region

#Region "Transactions"

        ''' <summary>
        ''' To Select Group
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function [Select]() As ExecutionResult
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 0
            Dim param(0) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupID", GroupID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_spSelectGroup", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' To Select non master Groups
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function SelectNonMasterGroups() As ExecutionResult
            Dim eReturnVal As ExecutionResult = Nothing
            'Const ARRAY_SIZE As Integer = 0
            Dim param(0) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupID", GroupID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_spSelectNonMasterGroups", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        Public Sub GetTransaction(ByRef lSQLTransaction As SqlTransaction)
            '  Dim lSQLTransaction As SqlTransaction
            Dim lSQLConnection As SqlConnection
            lSQLConnection = CoreFunctions.GetDatabaseConnection(strConfiguration)
            lSQLConnection.Open()
            lSQLTransaction = lSQLConnection.BeginTransaction

        End Sub

        ''' <summary>
        ''' To Insert Group
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function Insert() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupName", GroupName)
            param(1) = New SqlParameter("@ReturnVal", SqlDbType.Int)
            param(1).Direction = ParameterDirection.Output
            param(1).Value = 0
            param(2) = New SqlParameter("@IsMaster", Master)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_GC_SpInsertGroup", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function
        ''' <summary>
        ''' To delete Groups from CP_Groups.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function Delete() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 1
            Dim eReturnVal As ExecutionResult
            Dim param(1) As SqlClient.SqlParameter
            'Try
            param(0) = New SqlParameter("@Groupid", GroupID)
            param(1) = New SqlParameter("@Result", SqlDbType.Bit)
            param(1).Direction = ParameterDirection.Output
            param(1).Value = 0
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_CG_SpDeleteGroup", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try

        End Function
        ''' <summary>
        ''' To delete Groups from CP_Groups.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function IsMasterGroup() As Boolean
            'Const ARRAY_SIZE As Integer = 1
            Dim eReturnVal As ExecutionResult
            Dim param(1) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@Groupid", GroupID)
            param(1) = New SqlParameter("@IsMaster", SqlDbType.Bit)
            param(1).Direction = ParameterDirection.Output
            param(1).Value = 0
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_SpIsMaster", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal.ResultParamenters(1).Value
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try

        End Function
        ''' <summary>
        ''' To Select All Custodians
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function SelectAllCustodian() As ExecutionResult
            '  Const ARRAY_SIZE As Integer = 1
            'Try
            Dim eReturnVal As ExecutionResult
            'Dim param(ARRAY_SIZE) As SqlClient.SqlParameter
            'param(0) = New SqlParameter("@GroupName", GroupName)
            'param(1) = New SqlParameter("@ReturnVal", SqlDbType.Int)
            'param(1).Direction = ParameterDirection.Output
            'param(1).Value = 0
            eReturnVal = CoreFunctions.Execute(strConfiguration, "GetAllCustodian", CommandType.StoredProcedure, ReturnTypes.DataSet)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally

            'End Try
        End Function

        ''' <summary>
        ''' To Search Custodian
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function SearchCustodian() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@name", CustodianName)
            param(1) = New SqlParameter("@City", City)
            param(2) = New SqlParameter("@country", Country)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_spSearchCustodian", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' To Insert Custodians
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function InsertCustodianName() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@CustodianName", CustodianName)
            param(1) = New SqlParameter("@Active", IIf(Active, 1, 0))
            param(2) = New SqlParameter("@ReturnVal", SqlDbType.Int)
            param(2).Direction = ParameterDirection.Output
            param(2).Value = 0
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_spInsertCustodian", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' To Update Custodian Name
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function UpdateCustodianName() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            ' Try


            param(0) = New SqlParameter("@CustodianID", CustodianID)
            param(1) = New SqlParameter("@CustodianName", CustodianName)
            param(2) = New SqlParameter("@Active", Active)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_spUpdateCustodianName", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function
        ''' <summary>
        ''' checks the Existance Of group
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function IsGroupExists(ByVal pintGroupID As Integer, ByVal pstrGroupName As String) As Boolean
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupID", pintGroupID)
            param(1) = New SqlParameter("@GroupName", pstrGroupName)
            param(2) = New SqlParameter("@IsExists", SqlDbType.Bit)
            param(2).Direction = ParameterDirection.Output
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_GC_Sp_IsGroupAlreadyExists", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal.ResultParamenters(2).Value
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' checks the Existance Of group
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function IsMasterGroupExists(ByVal pintGroupID As Integer, ByRef lstrGroupName As String) As Boolean
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupID", pintGroupID)
            param(1) = New SqlParameter("@IsMasterGroupExists", SqlDbType.Bit)
            param(2) = New SqlParameter("@GroupName", SqlDbType.VarChar, 225)
            param(1).Direction = ParameterDirection.Output
            param(2).Direction = ParameterDirection.Output
            eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_GC_spIsMasterGroupExists", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            lstrGroupName = eReturnVal.ResultParamenters(2).Value
            Return eReturnVal.ResultParamenters(1).Value

            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function
        ''' <summary>
        ''' To Update Group
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function Update() As ExecutionResult
            'Const ARRAY_SIZE As Integer = 2
            Dim eReturnVal As ExecutionResult
            Dim param(2) As SqlClient.SqlParameter
            'Try

            param(0) = New SqlParameter("@GroupID", GroupID)
            param(1) = New SqlParameter("@GroupName", GroupName)
            param(2) = New SqlParameter("@IsMaster", Master)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_GC_spUpdateGroup", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Return eReturnVal
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' To Add and Update entity Address
        ''' </summary>
        ''' <param name="palPhoneDetails"></param>
        ''' <param name="pSQLTransaction"></param>
        ''' <remarks></remarks>
        ''' 

        Public Sub AddUpdateAddress(ByVal palPhoneDetails As ArrayList, Optional ByVal pSQLTransaction As SqlTransaction = Nothing)

            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(12) As SqlClient.SqlParameter
            Dim objSqlPhoneParam(5) As SqlClient.SqlParameter
            Dim eReturnPhoneVal As ExecutionResult = Nothing
            'Try

            param(0) = New SqlParameter("@EntityID", EntityId)
            param(1) = New SqlParameter("@EntityValueID", EntityValueId)
            param(2) = New SqlParameter("@City", City)
            param(3) = New SqlParameter("@State", State)
            param(4) = New SqlParameter("@CountryID", CountryID)
            param(5) = New SqlParameter("@Region", Region)
            param(6) = New SqlParameter("@Street1", Address)
            param(7) = New SqlParameter("@Street2", Address2)
            param(8) = New SqlParameter("@PostalCode", PostCode)
            param(9) = New SqlParameter("@County", County)
            param(10) = New SqlParameter("@EMail", EmailAddress)
            param(11) = New SqlParameter("@IsActive", Active)
            param(12) = New SqlParameter("@AddressDetailID", SqlDbType.Int)
            param(12).Direction = ParameterDirection.Output
            param(12).Value = 0
            If pSQLTransaction Is Nothing Then
                eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_SpAddUpdateEntityAddress", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            Else
                eReturnVal = CoreFunctions.Execute(pSQLTransaction, "CP_SpAddUpdateEntityAddress", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
            End If

            Dim iAddressDetailID As Integer = eReturnVal.ResultParamenters(12).Value

            For Each lobjPhoneDetails As Hashtable In palPhoneDetails

                'If all the Fields Are blank then, no need to add Phone Detail
                ' If Not (lobjPhoneDetails.Item("Country") = "" And lobjPhoneDetails.Item("Area") = "" And lobjPhoneDetails.Item("LocalNumber") = "" And lobjPhoneDetails.Item("Extension") = "") Then

                objSqlPhoneParam(0) = New SqlParameter("@PhoneTypeID", lobjPhoneDetails.Item("PhoneTypeId"))
                objSqlPhoneParam(1) = New SqlParameter("@Country", lobjPhoneDetails.Item("Country"))
                objSqlPhoneParam(2) = New SqlParameter("@Area", lobjPhoneDetails.Item("Area"))
                objSqlPhoneParam(3) = New SqlParameter("@Local", lobjPhoneDetails.Item("LocalNumber"))
                objSqlPhoneParam(4) = New SqlParameter("@Extension", lobjPhoneDetails.Item("Extension"))
                objSqlPhoneParam(5) = New SqlParameter("@AddressDetailID", iAddressDetailID)
                If pSQLTransaction Is Nothing Then
                    eReturnPhoneVal = CoreFunctions.Execute(strConfiguration, "Cp_SpEntityInsertPhoneDetails", CommandType.StoredProcedure, ReturnTypes.NonQuery, objSqlPhoneParam)
                Else
                    eReturnPhoneVal = CoreFunctions.Execute(pSQLTransaction, "Cp_SpEntityInsertPhoneDetails", CommandType.StoredProcedure, ReturnTypes.NonQuery, objSqlPhoneParam)
                End If

                ' End If

            Next

            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            '    objSqlPhoneParam = Nothing
            '    eReturnPhoneVal = Nothing
            'End Try
        End Sub

        ''' <summary>
        ''' To Get Entity Address Detail
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function GetAddressDetails() As DataSet
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(1) As SqlClient.SqlParameter
            'Try


            param(0) = New SqlParameter("@EntityID", EntityId)
            param(1) = New SqlParameter("@EntityValueID", EntityValueId)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_SpGetEntityAddressDetail", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            Return (CType(eReturnVal.ResultObject, DataSet))
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

        ''' <summary>
        ''' To Get Address Detail of investor name
        ''' </summary>
        ''' <returns>Returns Structure containing result sets and Exceptions if any</returns>
        ''' <remarks></remarks>
        ''' 

        Public Function GetAddressDetailsInvestor() As DataSet
            Dim eReturnVal As ExecutionResult
            Dim param(0) As SqlClient.SqlParameter
            'Try


            'param(0) = New SqlParameter("@EntityID", EntityId)
            param(0) = New SqlParameter("@EntityValueID", EntityValueId)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_SpGetAddressDetailsInvestor", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
            Return (CType(eReturnVal.ResultObject, DataSet))
            'Catch ex As Exception
            '    Throw ex
            'Finally
            '    param = Nothing
            'End Try
        End Function

#End Region

    End Class

End Namespace


